The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications, so it’s about time we dig in and check it out.
The Core Methods:
There are following three core methods
1. openDatabase: This method creates the database object either using existing database or creating new one.
transaction:This method give us the ability to control a transaction and performing either commit or rollback based on the situation.
2. executeSql:This method is used to execute actual SQL query.
Opening Database:
The openDatabase method takes care of opening a database if it already exists, this method will create it if it already does not exist.
To create and open a database, use the following code:
var database = openDatabase('msdb', '1.0', 'HTML5 Database', 2 * 1024 * 1024);
Above method took following five parameters:
1. Database name
2. Version number
3. Text description
4. Size of database
5. Creation callback
The last and 5th argument, creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly versioned.
Executing queries:
To execute a query you use the database.transaction() function. This function needs a single argument, which is a function that takes care of actually executing the query as follows:
var database = openDatabase('msdb', '1.0', 'HTML5 Database', 2 * 1024 * 1024);
var msg;
database.transaction(function (t) {
t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');
});The above query will create a table called ‘emp’ in 'msdb' database.
INERT Operation:
To create entries into the table we add simple SQL query in the above example as follows:
database.transaction(function (t) {
t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (1,"A",30000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (2,"B",20000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (3,"C",10000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (4,"D",5000)');
});READ Operation:
To read already existing records we use a callback to capture the results as follows:
var database = openDatabase('msdb', '1.0', 'HTML5 Database', 2 * 1024 * 1024);
database.transaction(function (t) {
t.executeSql('SELECT * FROM Emp', [], function (t, results) {
var l = results.rows.length, i;
msg = "<p>Records: " + l + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < l; i++) {
msg = "<p><b>" + results.rows.item(i).ID + " " + results.rows.item(i).Name + " " + results.rows.item(i).Salary + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});Complete Example
So finally, let us keep this example in full-fledged HTML5 document as follows and try to run it with Safari browser.
<!DOCTYPE>
<html>
<head>
<title>HTML5 Web Database</title>
<script type="text/javascript">
var database = openDatabase('msdb', '1.0', 'HTML5 Database', 2 * 1024 * 1024);
var msg;
database.transaction(function (t) {
t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (1,"A",30000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (2,"B",20000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (3,"C",10000)');
t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (4,"D",5000)');
msg = '<p>Table created and record inserted.</p>';
document.querySelector('#status').innerHTML = msg;
});
database.transaction(function (t) {
t.executeSql('SELECT * FROM Emp', [], function (t, results) {
var l = results.rows.length, i;
msg = "<p>Records: " + l + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < l; i++) {
msg = "<p><b>" + results.rows.item(i).ID + " " + results.rows.item(i).Name + " " + results.rows.item(i).Salary + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
</script>
</head>
<body>
<div id="status">
Status Message</div>
</body>
</html>
Output
Note:-
1. Web SQL Database will work in latest version of Safari, Chrome and Opera.
2. Version number 1.0 of ‘openDatabase’ supported by Chrome, Safari and Opera with latest version.
3. Version number 2.0 of ‘openDatabase’ supported by Opera and Safari with latest version.
4. Database is created by default on local. That means database are created in client system at ‘AppData’ folder. You can find this place by below simple steps
1) Press windows key + r
2) Paste %LocalAppData% and press Enter button
Now you can see in your system ‘AppData’ place. In this folder you see all supported HTML5 browser folder in ‘Local’ folder.
5. For example in my system ‘Safari’ browser database path is
C:\Users\Ms\AppData\Local\Apple Computer\Safari\Databases\http_localhost_53549
Here you see ‘.db’ file if you opened ‘HTML5 Web SQL’ on Safari browser.
6. ‘.db’ is a SQLite Database file. SQLite Database browser is a light GUI editor for SQLite databases, built on top of Qt. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface.
7. If you want to open ‘.db’ file follow below steps.
The ‘SQLite Database Browser’ can be downloaded from different – different sources. Once you download and extract the software, double click on the executable name SQLite Database Browser. After that follow the steps given below.
Step 1: Click on File -> Open and select the ‘.db’ file you want to open.
Step 2: If the file is a proper SQLite Database, you will be able to view the database tables and structure in the SQLite Database Browser as shown below picture.
Sometimes when you open a ‘.db’ file, you might see an error saying that the file is not a SQLite Database. In those cases, you can try and open the file in a text editor to view its contents. If you are running windows, you can also try to open the file with Microsoft Access. Remember that ‘thumbs.db’ is not a database file and cannot be opened using the above software.
Rahul Kesharwani
25-May-2020Very good Information, thank you so much